package net.vsame.sqlmanger.mysql;

import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import net.vsame.sqlmanger.helper.UserHelper;
import net.vsame.url2sql.domain.Url2SqlContext;
import net.vsame.url2sql.helper.SqlHelper;
import net.vsame.url2sql.helper.WebHelper;

public class DataBase {
	
	private Url2SqlContext c;

	public DataBase(){
		c = WebHelper.getContext();
	}
	
	private HashSet<String> getExistDateBase(){
		List<Map<String, Object>> list = SqlHelper.query("show databases", null);
		HashSet<String> set = new HashSet<String>();
		for (Map<String, Object> map : list) {
			String databaseName = map.get("Database")+"";
			set.add(databaseName);
		}
		return set;
	}
	
	private Long createDataBase(String name, String reason){
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("createTime", new Date());
		map.put("name", name);
		map.put("reason", reason);
		String sql = "INSERT INTO `database_t` (`databaseName`, createTime, reason) VALUES (${name}, ${createTime}, ${reason})";
		return SqlHelper.executeGeneratedKey(sql, map);
	}
	
	public void list(){
		HashSet<String> set = getExistDateBase();
		String sql = "SELECT d.*, a.applyCount 'applyCount' FROM `database_t` d LEFT JOIN " + 
						"(SELECT a.databaseId, count(a.databaseId) 'applyCount' FROM `database_t` d, database_apply_t a " +
						" WHERE d.id=a.databaseId and a.`status`='APPLY' GROUP BY a.databaseId) a " +
					" on d.id=a.databaseId WHERE d.removed='N'";
		List<Map<String, Object>> database = SqlHelper.query(sql, null);
		for (Map<String, Object> map : database) {
			Object databaseName = map.get("databaseName");
			String key = "exist";
			if(set.contains(databaseName)){
				map.put(key, true);
				set.remove(databaseName);
			}else{
				map.put(key, false);
			}
		}
		String msg = "自动导入";
		for(String key : set){
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("databaseName", key);
			map.put("createTime", new Date());
			map.put("exist", true);
			map.put("reason", msg);
			Long id = createDataBase(key, msg);
			map.put("id", id);
			database.add(map);
		}
		c.put("data", database);
	}
	
	public void addDatabase(){
		Long id = createDataBase(c.getParam("name"), c.getParam("reason"));
		
		String userId = UserHelper.getUserId()+"";
		String sql = "INSERT INTO `database_apply_t` (`databaseId`, `operate`, `reason`, `applyId`, `status`, createTime) VALUES (" + id +
				", 'CREATE', ${reason}, " + userId + 
				", 'APPLY', ${createTime})";
		Map<String, Object> map = new HashMap<String, Object>(); 
		map.put("createTime", new Date());
		SqlHelper.execute(sql, map);
	}
	
	public void applyInfo(){
		String sql = "SELECT a.*, m.name 'handleName' from (SELECT d.*, a.name 'applyName' FROM `database_apply_t` d, member_t a where d.applyId=a.id and databaseId=${id} ORDER BY d.id desc) a LEFT JOIN member_t m on a.handlerId=m.id";
		List<Map<String, Object>> data = SqlHelper.query(sql, null);
		c.put("data", data);
	}
	
	public void applyOperate(){
		String operate = c.getParam("operate");
		if("DROP".equalsIgnoreCase(operate) || "BACKUP".equalsIgnoreCase(operate)){
			
		}else{
			c.putError(1, "操作非法");
			return ;
		}
		//
		String userId = UserHelper.getUserId()+"";
		String sql = "INSERT INTO `database_apply_t` (`databaseId`, `operate`, `reason`, `applyId`, `status`, createTime) VALUES (${id}" +
				", ${operate}, ${reason}, " + userId + 
				", 'APPLY', ${createTime})";
		Map<String, Object> map = new HashMap<String, Object>(); 
		map.put("createTime", new Date());
		SqlHelper.execute(sql, map);
	}
	
	//=================表列表=====
	private HashSet<String> getExistTable(String database){
		String nowDatabase = SqlHelper.queryOne("select database() `database`", null).get("database")+"";
		SqlHelper.execute("use " + database, null);
		List<Map<String, Object>> list = SqlHelper.query("show TABLES", null);
		SqlHelper.execute("use " + nowDatabase, null);
		
		HashSet<String> set = new HashSet<String>();
		for (Map<String, Object> map : list) {
			for(Map.Entry<String, Object> m : map.entrySet()){
				set.add(m.getValue()+"");
			}
		}
		return set;
	}
	
	public void database(){
		String sql = "SELECT * FROM `database_t` where id=${0}";
		Map<String, Object> map = SqlHelper.queryOne(sql , null);
		if(map == null){
			c.putError(1, "数据库不存在");
		}
		
	}
	
	public static void main(String[] args) {
		WebHelper.init(null, null);
		Map<String, Object> nowDatabase = SqlHelper.queryOne("select database() `database`", null);
		List<Map<String, Object>> list = SqlHelper.query("show TABLES", null);
		System.out.println(list);
		System.out.println(nowDatabase);

	}
}
